import mysql.connector  
from datetime import datetime, timedelta  
# 示例数据库配置  
db_config = {  
    'host': 'your_host',  
    'user': 'your_user',  
    'password': 'your_password',  
    'database': 'attendance_system'  
}
class EmployeeAttendance:  
    def __init__(self, db_config=db_config):  
        self.db_config = db_config  
        self.connection = None  
        self.cursor = None  
  
    def connect_to_db(self):  
        self.connection = mysql.connector.connect(**self.db_config)  
        self.cursor = self.connection.cursor()  
    def create_db(self):
        self.connect_to_db()#连接数据库
        query = """  
        CREATE TABLE IF NOT EXISTS employees_attendance (  
            id INT AUTO_INCREMENT PRIMARY KEY,  
            employee_id VARCHAR(20) UNIQUE NOT NULL,  
            name VARCHAR(100) NOT NULL,  
            department VARCHAR(100) NOT NULL,  
            punch_in_time DATETIME,  
            is_late VARCHAR(10) DEFAULT '否',  
            punch_out_time DATETIME,  
            is_early_leave VARCHAR(10) DEFAULT '否',  
            work_duration TIME,  
            is_absent VARCHAR(10) DEFAULT '否'  
        )  
        """  
        self.cursor.execute(query)  
        self.connection.commit()  
        self.close_db_connection()  #断开链接


    def close_db_connection(self):  
        if self.cursor:  
            self.cursor.close()  
        if self.connection and self.connection.is_connected():  
            self.connection.close()  
  
    def traverse_employees(self):  
        self.connect_to_db()  
        self.cursor.execute("SELECT * FROM employees_attendance")  
        results = self.cursor.fetchall()
        for row in results:   #这里是打印，其实不需要的
            print(row)  
        self.close_db_connection()  
        return results
  
    def insert_employee_info(self, employee_id, name, department):  
        self.connect_to_db()  
        query = "INSERT INTO employees_attendance (employee_id, name, department) VALUES (%s, %s, %s)"  
        self.cursor.execute(query, (employee_id, name, department))  
        self.connection.commit()  
        self.close_db_connection()  
  
    def insert_punch_in_info(self, employee_id, punch_in_time, is_late):  
        self.connect_to_db()  
        query = "UPDATE employees_attendance SET punch_in_time = %s, is_late = %s WHERE employee_id = %s"  
        self.cursor.execute(query, (punch_in_time, is_late, employee_id))  
        self.connection.commit()  
        self.close_db_connection()  
  
    def insert_punch_out_info(self, employee_id, punch_out_time, is_early_leave, work_duration):  
        self.connect_to_db()  
        query = "UPDATE employees_attendance SET punch_out_time = %s, is_early_leave = %s, work_duration = %s WHERE employee_id = %s"  
        self.cursor.execute(query, (punch_out_time, is_early_leave, work_duration, employee_id))  
        self.connection.commit()  
        self.close_db_connection()  
    #判断是否缺勤
    def update_absent_status(self, employee_id, is_absent):  
        self.connect_to_db()  
        query = "UPDATE employees_attendance SET is_absent = %s WHERE employee_id = %s"  
        self.cursor.execute(query, (is_absent, employee_id))  
        self.connection.commit()  
        self.close_db_connection()  
  
  
'''
# 示例使用  
employee = EmployeeAttendance(db_config)  
employee.create_db()#创建数据库
results = employee.traverse_employees()  # 遍历所有员工信息  
print(results)
employee.insert_employee_info('001', '张三', '销售部')  # 插入员工信息  
employee.insert_punch_in_info('001', datetime.now(), '否')  # 插入上班打卡时间和是否迟到  
# 假设下班时间为当前时间+8小时，工作时长为8小时，这里为简化示例直接赋值  
employee.insert_punch_out_info('001', datetime.now() + timedelta(hours=8), '是', timedelta(hours=8))  # 插入下班打卡时间和是否早退，
'''
